﻿
-- ****************************************************************************************
--                                 SYSTEMVERSIONPLUS
-- ****************************************************************************************

UPDATE 
	SystemVersionPlus 
SET 
	Major = 1, Minor = 40, Build = 1, Revision = 0 
WHERE 
	Major = 1 AND Minor = 40 AND Build = 0


-- ****************************************************************************************
--                                    SYSTEM SETTINGS
-- ****************************************************************************************

IF NOT EXISTS (SELECT 1 FROM SystemSettingValue WHERE SystemSettingName = 'WebPlusMarkbookDisplay')
BEGIN
	INSERT INTO	
		SystemSettingValue
			(SystemSettingID, SystemSettingName, SystemSettingDescription, DataType, DefaultValue, SystemSettingValue, SystemSettingCategoryID)
	VALUES
		((SELECT MAX(SystemSettingID)+1 FROM SystemSettingValue), 'WebPlusMarkbookDisplay', 'Controls the Markbook functionality (when MB not Licensed) as follows: SHOW=Show the Markbook Popup  HIDE=Hide the Markbook Popup', 'System.String', 'HIDE', 'HIDE', 1)
END
ELSE
BEGIN
    UPDATE SystemSettingValue 
    SET SystemSettingDescription = 'Controls the Markbook functionality (when MB not Licensed) as follows: SHOW=Show the Markbook Popup  HIDE=Hide the Markbook Popup' 
    WHERE SystemSettingName = 'WebPlusMarkbookDisplay'
END
GO



-- ****************************************************************************************
--                         Markbook AutoComplete - Table Changes
-- ****************************************************************************************

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'OGP_MarkbookOption' AND COLUMN_NAME = 'HelpText')
BEGIN
    ALTER TABLE OGP_MarkbookOption ADD HelpText VARCHAR(1000) NULL;
END

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'OGP_MarkbookOption' AND COLUMN_NAME = 'OGP_MarkbookOptionDetailID')
BEGIN
    ALTER TABLE OGP_MarkbookOption ADD OGP_MarkbookOptionDetailID INT NULL;
END


-- ****************************************************************************************
--                         Markbook AutoComplete - DATA CHANGES
-- ****************************************************************************************

--we already have IDs (1,2) so make the MainQuestion=0
IF NOT EXISTS (SELECT 1 FROM OGP_MarkbookOption WHERE ID = 0)
BEGIN
  INSERT INTO OGP_MarkbookOption VALUES (0, 'Type of Auto Complete used for this Course', 'Helptext TBD', null);
  INSERT INTO OGP_MarkbookOptionDetail VALUES (0, 'PMD', 1)
  INSERT INTO OGP_MarkbookOptionDetail VALUES (0, 'A*-D', 2)
  INSERT INTO OGP_MarkbookOptionDetail VALUES (0, 'Pass Only', 3)
END

IF NOT EXISTS (SELECT 1 FROM OGP_MarkbookOption WHERE ID = 3)
BEGIN
  INSERT INTO OGP_MarkbookOption VALUES (3, 'Update Grade to A*-D dynamically based on Criteria Completion (prefixed A*,A,B,C,D)', Null, Null);
  INSERT INTO OGP_MarkbookOptionDetail VALUES (3, 'Units', 1)
  INSERT INTO OGP_MarkbookOptionDetail VALUES (3, 'Assessments', 2)
  INSERT INTO OGP_MarkbookOptionDetail VALUES (3, 'Units & Assessments', 3)
END

IF NOT EXISTS (SELECT 1 FROM OGP_MarkbookOption WHERE ID = 4)
BEGIN
  INSERT INTO OGP_MarkbookOption VALUES (4, 'Update Complete dynamically based on Criteria Completion (prefixed A*,A,B,C,D)', Null, Null);
  INSERT INTO OGP_MarkbookOptionDetail VALUES (4, 'Units', 1)
  INSERT INTO OGP_MarkbookOptionDetail VALUES (4, 'Assessments', 2)
  INSERT INTO OGP_MarkbookOptionDetail VALUES (4, 'Units & Assessments', 3)
END

IF NOT EXISTS (SELECT 1 FROM OGP_MarkbookOption WHERE ID = 5)
BEGIN
  INSERT INTO OGP_MarkbookOption VALUES (5, 'Update Grade to P dynamically based on Criteria Completion (any prefix)', Null, Null);
  INSERT INTO OGP_MarkbookOptionDetail VALUES (5, 'Units', 1)
  INSERT INTO OGP_MarkbookOptionDetail VALUES (5, 'Assessments', 2)
  INSERT INTO OGP_MarkbookOptionDetail VALUES (5, 'Units & Assessments', 3)
END

IF NOT EXISTS (SELECT 1 FROM OGP_MarkbookOption WHERE ID = 6)
BEGIN
  INSERT INTO OGP_MarkbookOption VALUES (6, 'Update Complete dynamically based on Criteria Completion (any prefix)', Null, Null);
  INSERT INTO OGP_MarkbookOptionDetail VALUES (6, 'Units', 1)
  INSERT INTO OGP_MarkbookOptionDetail VALUES (6, 'Assessments', 2)
  INSERT INTO OGP_MarkbookOptionDetail VALUES (6, 'Units & Assessments', 3)
END


--XRef Options with Answers
UPDATE OGP_MarkbookOption SET OGP_MarkbookOptionDetailID = (SELECT ID FROM OGP_MarkbookOptionDetail WHERE MArkbookOptionID = 0 AND Description = 'PMD') WHERE ID = 1
UPDATE OGP_MarkbookOption SET OGP_MarkbookOptionDetailID = (SELECT ID FROM OGP_MarkbookOptionDetail WHERE MArkbookOptionID = 0 AND Description = 'PMD') WHERE ID = 2

UPDATE OGP_MarkbookOption SET OGP_MarkbookOptionDetailID = (SELECT ID FROM OGP_MarkbookOptionDetail WHERE MArkbookOptionID = 0 AND Description = 'A*-D') WHERE ID = 3
UPDATE OGP_MarkbookOption SET OGP_MarkbookOptionDetailID = (SELECT ID FROM OGP_MarkbookOptionDetail WHERE MArkbookOptionID = 0 AND Description = 'A*-D') WHERE ID = 4

UPDATE OGP_MarkbookOption SET OGP_MarkbookOptionDetailID = (SELECT ID FROM OGP_MarkbookOptionDetail WHERE MArkbookOptionID = 0 AND Description = 'Pass Only') WHERE ID = 5
UPDATE OGP_MarkbookOption SET OGP_MarkbookOptionDetailID = (SELECT ID FROM OGP_MarkbookOptionDetail WHERE MArkbookOptionID = 0 AND Description = 'Pass Only') WHERE ID = 6


--HelpText
UPDATE OGP_MarkbookOption SET HelpText = 'This option allows you to automate the grades for {unitplural} and/or {assessmentplural} based on the compilation of {criteria} within those {unitplural}/{assessmentplural}. The rule will look for {criteria} starting with the letters P, M or D - other {criteria} will be ignored by the rule but may still be ticked as complete. If applied, the only grade which may be manually entered for a {unit} and/or {assessment} is ''NP'' to signify ''Near Pass'' - there is no need to set up {criteria} with the code ''NP'' within {unitplural}.' WHERE ID = 1;
UPDATE OGP_MarkbookOption SET HelpText = 'This option allows you to set the ''Completion'' tickbox to be ticked if at least all of the {criteria} prefixed ''P'' within a {unit} and/or {assessment} are ticked as complete.' WHERE ID = 2;



-- ****************************************************************************************
--                            InYear Grade - Table CHanges
-- ****************************************************************************************

--Add the Obsolete Flag to the UnGradedQualifications_PointsGrades table
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'UnGradedQualifications_PointsGrades' AND COLUMN_NAME = 'Obsolete')
    BEGIN
        ALTER TABLE UnGradedQualifications_PointsGrades
        ADD Obsolete BIT NOT NULL
        CONSTRAINT DF_UnGradedQualifications_PointsGrades_Obsolete DEFAULT (0)
    END
GO

--Add the Obsolete Flag to the GradingScheme_NoGrade table
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'GradingScheme_NoGrade' AND COLUMN_NAME = 'Obsolete')
    BEGIN
        ALTER TABLE GradingScheme_NoGrade
        ADD Obsolete BIT NOT NULL
        CONSTRAINT DF_GradingScheme_NoGrade_Obsolete DEFAULT (0)
    END
GO



-- ****************************************************************************************
--                            Import - table changes
-- ****************************************************************************************

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ImportHoldingTable_QOE' AND COLUMN_NAME = 'Grade' AND CHARACTER_MAXIMUM_LENGTH = 20)
    BEGIN
        ALTER TABLE ImportHoldingTable_QOE
        ALTER COLUMN Grade VARCHAR(50) NULL
    END
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ImportHoldingTable_EngMatQOE' AND COLUMN_NAME = 'Grade' AND CHARACTER_MAXIMUM_LENGTH = 20)
    BEGIN
        ALTER TABLE ImportHoldingTable_EngMatQOE
        ALTER COLUMN Grade VARCHAR(50) NULL
    END
GO


